package com.weixin.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.IPurchaseDao;
import com.weixin.entity.Purchase;
import com.weixin.util.C3p0Util;

public class PurchaseDaoImpl implements IPurchaseDao {
	C3p0Util c3p0=new C3p0Util();
	
	@Override
	public List<Purchase> selectAll() throws SQLException {
		String sql="SELECT * from purchase_tb where is_show=1";
		return c3p0.getqr().query(sql, new BeanListHandler<Purchase>(Purchase.class));
	}

	@Override
	public Purchase selectById(int purchase_id) throws SQLException {
		String sql="SELECT * from purchase_tb where purchase_id=?";
		return c3p0.getqr().query(sql,new BeanHandler<Purchase>(Purchase.class),purchase_id);
	}

	@Override
	public boolean insertPurchase(Purchase purchase) throws SQLException {
		String sql="insert into purchase_tb(goods_id,number,purchase_people_id,supplier_id,purchase_time,inspect,storehouse_id,remark) values(?,?,?,?,?,?,?,?)";
		int i=c3p0.getqr().update(sql, purchase.getGoods_id(),purchase.getNumber(),purchase.getPurchase_people_id(),purchase.getSupplier_id(),purchase.getPurchase_time(),purchase.getInspect(),purchase.getStorehouse_id(),purchase.getRemark());
		return i>0;
	}

	@Override
	public boolean updatePurchase(Purchase purchase) throws SQLException {
		String sql="update purchase_tb set goods_id=?,number=?,purchase_people_id=?,supplier_id=?,purchase_time=?,inspect=?,storehouse_id=?,remark=? where purchase_id=?";
		int i=c3p0.getqr().update(sql,purchase.getGoods_id(),purchase.getNumber(),purchase.getPurchase_people_id(),purchase.getSupplier_id(),purchase.getPurchase_time(),purchase.getInspect(),purchase.getStorehouse_id(),purchase.getRemark(),purchase.getPurchase_id());
		return i>0;
	}

	@Override
	public boolean deleteById(int purchase_id) throws SQLException {
		String sql="UPDATE purchase_tb set is_show=0 where purchase_id=?";
		int i=c3p0.getqr().update(sql,purchase_id);
		return i>0;
	}

	@Override
	public List<Purchase> get_all_about(int pageSize, int pageNum) throws SQLException {
		String sql="SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.inspect=0 and p.is_show=1 limit ?,?";
		return c3p0.getqr().query(sql, new BeanListHandler<Purchase>(Purchase.class),(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public Purchase getAllAboutById(int purchase_id) throws SQLException {
		String sql="SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.inspect=0 and p.purchase_id=?";
		return c3p0.getqr().query(sql, new BeanHandler<Purchase>(Purchase.class),purchase_id);
	}

	@Override
	public List<Purchase> getInspect(int pageSize, int pageNum) throws SQLException {
		String sql="SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.inspect=1  and p.storehouse_id=10 and p.is_show=1 limit ?,?";
		return c3p0.getqr().query(sql, new BeanListHandler<Purchase>(Purchase.class),(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public Purchase getInpectById(int purchase_id) throws SQLException {
		String sql="SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.purchase_id=? and p.inspect=1 and p.storehouse_id=10";
		return c3p0.getqr().query(sql, new BeanHandler<Purchase>(Purchase.class),purchase_id);
	}

	@Override
	public List<Purchase> getInStroehouse(int pageSize, int pageNum) throws SQLException {
		String sql="SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.inspect=1 and p.storehouse_id!=10 and p.is_show=1 limit ?,?";
		return c3p0.getqr().query(sql, new BeanListHandler<Purchase>(Purchase.class),(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public List<Purchase> getByCondition(Map<String, String[]> parameMap) throws SQLException {
		//创建一个List放所有参数的值
		List<String> valueList=new ArrayList<>();
		
		//创建一个StringBuffer的待拼接的sql
		StringBuffer sql=new StringBuffer("SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.inspect=1 and p.storehouse_id!=10 and p.is_show=1");
		//遍历map
		Set<Entry<String, String[]>> entrySet = parameMap.entrySet();
		for (Entry<String, String[]> entry : entrySet) {
			//System.out.println(entry.getKey());
			//System.out.println(entry.getValue()[0]);
			String value=entry.getValue()[0];
			if("".equals(value.trim())) {
				//如果参数值为空字符串结束本次循环
				continue;
			}
			if("goods_name".equals(entry.getKey())){
				valueList.add("%"+value+"%");
				String key=entry.getKey();
				sql.append(" and g."+key+" like ?");
				System.out.println(sql.toString());
			}else if("purchase_name".equals(entry.getKey())){
				valueList.add("%"+value+"%");
				String key=entry.getKey();
				sql.append(" and p_p."+key+" like ?");
			}else if("supplier_".equals(entry.getKey())){
				valueList.add("%"+value+"%");
				String key=entry.getKey();
				sql.append(" and s."+key+" like ?");
			}
		}		
		return c3p0.getqr().query(sql.toString(), new BeanListHandler<Purchase>(Purchase.class),valueList.toArray());
	}

	@Override
	public int getInStorehouseTotal() throws SQLException {
		String sql="SELECT COUNT(*) FROM purchase_tb WHERE storehouse_id != 10";
		return (int)((long)c3p0.getqr().query(sql, new ScalarHandler()));
	}

	@Override
	public int getInspectTotal() throws SQLException {
		String sql="SELECT COUNT(*) FROM purchase_tb WHERE storehouse_id = 10 and inspect=1";
		return (int)((long)c3p0.getqr().query(sql, new ScalarHandler()));
	}

	@Override
	public int get_all_aboutTotal() throws SQLException {
		String sql="SELECT COUNT(*) FROM purchase_tb WHERE storehouse_id = 10 and inspect=0";
		return (int)((long)c3p0.getqr().query(sql, new ScalarHandler()));
	}

	@Override
	public List<Purchase> getAllAboutLike(String values) throws SQLException {
		String sql="SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.inspect=0 and p.is_show=1 and (g.goods_name like ? or p_p.purchase_name like ? or s.supplier_ like? or g.price like ?)";
		return c3p0.getqr().query(sql, new BeanListHandler<Purchase>(Purchase.class),"%"+values+"%","%"+values+"%","%"+values+"%","%"+values+"%");
		
	}

	@Override
	public List<Purchase> getLikeForInHouse(String values) throws SQLException {
		String sql="SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.inspect=1 and p.storehouse_id=10 and p.is_show=1 and (g.goods_name like ? or p_p.purchase_name like ? or s.supplier_ like? or g.price like ?)";
		return c3p0.getqr().query(sql, new BeanListHandler<Purchase>(Purchase.class),"%"+values+"%","%"+values+"%","%"+values+"%","%"+values+"%");
	}

	@Override
	public List<Purchase> getAllForRegain(int pageSize, int pageNum) throws SQLException {
		String sql="SELECT p.*,g.goods_name,g.price,g.unit,p_p.purchase_name,s.supplier_,sh.storehouse_name\r\n" + 
				"from goods_tb g,purchase_people_tb p_p,purchase_tb p,supplier_tb s,storehouse_tb sh\r\n" + 
				"where p.goods_id=g.goods_id \r\n" + 
				"and p.purchase_people_id=p_p.purchase_people_id \r\n" + 
				"and p.supplier_id=s.supplier_id \r\n" + 
				"and p.storehouse_id=sh.storehouse_id and p.is_show=0 limit ?,?";
		return c3p0.getqr().query(sql, new BeanListHandler<Purchase>(Purchase.class),(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public boolean regainById(int purchase_id) throws SQLException {
		String sql="UPDATE purchase_tb set is_show=1 where purchase_id=?";
		int i=c3p0.getqr().update(sql,purchase_id);
		return i>0;
	}

	@Override
	public boolean trueDeleteById(int purchase_id) throws SQLException {
		String sql="delete from purchase_tb where purchase_id=?";
		int i=c3p0.getqr().update(sql,purchase_id);
		return i>0;
	}

	@Override
	public int getTotalForRegain() throws SQLException {
		String sql="select count(*) from purchase_tb where is_show=0";
		return (int)((long)c3p0.getqr().query(sql, new ScalarHandler()));
	}



}
